﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Collections.ObjectModel;
using DBAccessLibrary.Interface;
using DBAccessLibrary.Model;
using System.Data;
using DataAccess;
using DataAccess.Entity;
using Skynet.CommonLibrary.Extension;
using DBAccessLibrary.Common;
using System.Windows.Media;

namespace DBAccessLibrary
{
    /// <summary>
    /// 常用数据库操作
    /// </summary>
    public class DatabaseOperations:IOperations
    {
        /// <summary>
        /// 构建数据源
        /// </summary>
        /// <param name="list"></param>
        /// <param name="log"></param>
        /// <param name="begioninvoke"></param>
        /// <returns></returns>
        public bool CreateDBEntity(ObservableCollection<DataBaseEntity> list, Action<string,Color> log, Action<Action> begioninvoke)
        {
            //判断当前连接是否可用
            if (SysCache.DBConnection == null)
            {
                log("当前连接不可用。", Colors.Red);
                return false;
            }
            string error = IsConnectDB(SysCache.DBConnection);
            if (error != string.Empty)
            {
                log("当前数据库无法连接。异常：" + error, Colors.Red);
                begioninvoke(() =>
                {
                    DBEntity selectdb = list.SelectMany(o => o.ChildNodes).Cast<DBEntity>().FirstOrDefault(o => o.Name == SysCache.DBConnection.DataBase && o.ConnectionSource.Server == SysCache.DBConnection.Server);
                    if (selectdb != null)
                    {
                        selectdb.Status = DBConnectStatus.Off;
                    }
                });
                return false;
            }
            //先删除 该节点，再 增加一个等待节点
            ServerEntity server = new ServerEntity(SysCache.DBConnection.Server)
                                      {
                                          ImageUrl = "/Images/Tree/computers.png"
                                      };

            DBEntity db = CreateDBEntity(SysCache.DBConnection);
            DBEntity test = new DBEntity("正在连接，请等待……")
                                {
                                    ImageUrl = "/Images/Tree/blue-loading.gif"
                                };
            //判断是目前是否存在相同的数据源节点，如果相同 则删除原有节点
            begioninvoke(() =>
                             {
                                 DataBaseEntity first = list.FirstOrDefault(o => o.Name == server.Name);
                                 if (first != null)
                                 {
                                     DataBaseEntity dbfirst = first.ChildNodes.FirstOrDefault(o => o.Name == db.Name);
                                     if (dbfirst != null)
                                     {
                                         first.ChildNodes.Remove(dbfirst);
                                     }
                                     server = first as ServerEntity;
                                 }
                                 else
                                 {
                                     list.Add(server);
                                 }

                                 if (server != null)
                                 {
                                     server.ChildNodes.Add(test);
                                     server.IsExpanded = true;
                                 }
                             });

           //读取数据
            SysCache.TableList = new List<TableEntity>();
            SysCache.ProcList = new List<ProcEntity>();
            SysCache.ViewList = new List<ViewEntity>();
            GetDbEntitys(log, SysCache.DBConnection, true,db);

            //加载节点，并将其他节点的状态 改变成关闭状态。
            begioninvoke(() =>
            {
                server.ChildNodes.Remove(test);
                server.ChildNodes.Add(db);
                //改变状态

                foreach (DBEntity dbEntity in list.SelectMany(o => o.ChildNodes).Cast<DBEntity>().Where(o => o.Status == DBConnectStatus.Connected))
                {
                    //如果数据库名字不相同 则设置为关闭状态 
                    if (dbEntity.Name != db.Name)
                        dbEntity.Status = DBConnectStatus.Off;
                    //如果数据库名字一样 而 IP 不同 则设置 为关闭状态 
                    else if (dbEntity.Name == db.Name && dbEntity.ConnectionSource.Server != db.ConnectionSource.Server)
                        dbEntity.Status = DBConnectStatus.Off;
                }

                db.Status = DBConnectStatus.Connected;

                db.IsExpanded = true;
            });
            log("数据库连接成功", Colors.Blue);
            return true;
        }
        
        #region 构建数据
        /// <summary>
        /// 从数据库读取 表，视图，存储过程等信息
        /// </summary>
        /// <param name="log"></param>
        /// <param name="connectModel"></param>
        /// <param name="isLoadChina"></param>
        /// <param name="db"></param>
        /// <returns></returns>
        private DbEntityCache GetDbEntitys(Action<string,Color> log,ConnectionModel connectModel,bool isLoadChina,DBEntity db = null)
        {
            DataSet chds = null;
            List<TableInfo> tinfo = new List<TableInfo>();
            List<ColumnInfo> cinfo = new List<ColumnInfo>();
            List<IndexInfo> iinfo = new List<IndexInfo>();
            List<RelationshipInfo> rinfo = new List<RelationshipInfo>();
            List<ViewInfo> vinfo = new List<ViewInfo>();
            List<ProcInfo> pinfo = new List<ProcInfo>();
            List<ProcParamInfo> ppinfo = new List<ProcParamInfo>();

            ConnectDB(connectModel, (connect, dataaccess) =>
                                                 {
                                                     log("获取数据库相关信息", Colors.Blue);

                                                     Action<string, Action> wirteError =
                                                         (msg, action) =>
                                                             {
                                                                 try
                                                                 {
                                                                     action();
                                                                 }
                                                                 catch (Exception ex)
                                                                 {
                                                                     log(msg + "获取失败.--" + ex.Message, Colors.Red);
                                                                 }
                                                             };
                                                     IDataStructure structue = dataaccess as IDataStructure;
                                                     if (structue == null)
                                                         return;
                                                     wirteError("表信息", () => structue.FillingTables(tinfo));

                                                     wirteError("列信息", () => structue.FillingColumns(cinfo));
                                                     wirteError("索引信息", () => structue.FillingIndexs(iinfo));
                                                     wirteError("关系信息", () => structue.FillingRelations(rinfo));
                                                     wirteError("视图信息", () => structue.FillingViews(vinfo));
                                                     wirteError("存储过程信息", () => structue.FillingProcs(pinfo));
                                                     wirteError("存储过程参数信息", () => structue.FillingProcParams(ppinfo));

                                                     if (isLoadChina)
                                                     {
                                                         //获取中文对照信息
                                                         wirteError("中文对照信息",
                                                                    () =>
                                                                    chds =
                                                                    dataaccess.ExecuteDataset(
                                                                        DBOperatorFactory.GetGrammar(connect).GetChTableSql));
                                                     }
                                                 }

                );

            log("构建实体集合", Colors.Blue);

            DescriptionEntity descEntity = CreateTableEntity(tinfo, cinfo, chds, rinfo, iinfo);
            DescriptionEntity viewEntity = CreateViewEntity(vinfo, cinfo);
            DescriptionEntity procEntity = CreateProcEntity(pinfo, ppinfo);

            if (db != null)
            {
                db.ChildNodes.Add(descEntity);
                db.ChildNodes.Add(viewEntity);
                db.ChildNodes.Add(procEntity);
            }

            DbEntityCache cache = new DbEntityCache
                                      {
                                          TableList = descEntity.ChildNodes.Cast<TableEntity>(),
                                          ViewList = viewEntity.ChildNodes.Cast<ViewEntity>(),
                                          ProcList = procEntity.ChildNodes.Cast<ProcEntity>()
                                      };
            return cache;
        }

        /// <summary>
        /// 创建数据库实体
        /// </summary>
        /// <param name="connect"></param>
        /// <returns></returns>
        private DBEntity CreateDBEntity(ConnectionModel connect)
        {
            DBEntity db = new DBEntity(connect.DataBase)
                              {
                                  ConnectionSource = connect,
                                  Status = DBConnectStatus.Connected
                              };
            return db;
        }
        /// <summary>
        /// 创建表和列实体
        /// </summary>
        /// <param name="tbds"></param>
        /// <param name="columnds"></param>
        /// <param name="chds"></param>
        /// <param name="relds"></param>
        /// <param name="indexds"></param>
        /// <returns></returns>
        private DescriptionEntity CreateTableEntity(List<TableInfo> tbds, List<ColumnInfo> columnds, DataSet chds, IEnumerable<RelationshipInfo> relds, IEnumerable<IndexInfo> indexds)
        {
            DescriptionEntity table = new DescriptionEntity("表")
                                          {
                                              ImageUrl = "/Images/Tree/tableGroup.png"
                                          };
            if (tbds != null && tbds.Count > 0)
            {
                foreach (TableInfo tbrow in tbds)
                {
                    TableEntity tbEntity = new TableEntity(tbrow.TableName)
                                               {
                                                   PrimaryKeyName = tbrow.PrimaryKeyName
                                               };

                    tbEntity.ChildNodes.Add(new TableInfoEntity("列") { ImageUrl = "/Images/Tree/columns.png" });
                    tbEntity.ChildNodes.Add(new TableInfoEntity("外键") { ImageUrl = "/Images/Tree/relationship.png" });
                    tbEntity.ChildNodes.Add(new TableInfoEntity("索引") { ImageUrl = "/Images/Tree/index.png" });

                    table.ChildNodes.Add(tbEntity);
                }


                ////获取该表中的所有列
                //先筛选主键和外键
                foreach (ColumnInfo columnrow in columnds.Where(o => o.IsForeignKey || o.IsPrimaryKey).OrderByDescending(o => o.IsPrimaryKey).ThenByDescending(o => o.IsForeignKey))
                {
                    ColumnInfo columnrow1 = columnrow;
                    DataBaseEntity tbentity = table.ChildNodes.FirstOrDefault(o => o.Name == columnrow1.TableName);
                    if (tbentity == null)
                        continue;
                    FieldEntity field = new FieldEntity(columnrow.ColumnName)
                    {
                        TypeName = columnrow.TypeName,
                        Scale = columnrow.DataScale,
                        Length = columnrow.Length,
                        Index = columnrow.ColumnIndex,
                        IsNull = columnrow.IsNull,
                        DefaultValue = columnrow.DefaultValue,
                        IsForeignKey = columnrow.IsForeignKey,
                        IsPrimaryKey = columnrow.IsPrimaryKey,
                        TableName = columnrow.TableName
                    };
                    tbentity.ChildNodes[0].ChildNodes.Add(field);
                }
                foreach (ColumnInfo columnrow in columnds.Where(o => !o.IsForeignKey && !o.IsPrimaryKey))
                {
                    ColumnInfo columnrow1 = columnrow;
                    DataBaseEntity tbentity= table.ChildNodes.FirstOrDefault(o => o.Name == columnrow1.TableName);
                    if (tbentity == null)
                        continue;
                    FieldEntity field = new FieldEntity(columnrow.ColumnName)
                                            {
                                                TypeName = columnrow.TypeName,
                                                Scale = columnrow.DataScale,
                                                Length = columnrow.Length,
                                                Index = columnrow.ColumnIndex,
                                                IsNull = columnrow.IsNull,
                                                DefaultValue = columnrow.DefaultValue,
                                                IsForeignKey = columnrow.IsForeignKey,
                                                IsPrimaryKey =columnrow.IsPrimaryKey,
                                                TableName = columnrow.TableName
                                            };
                    tbentity.ChildNodes[0].ChildNodes.Add(field);
                }

                //获取关系信息
                CreateRelationshipEntity(table, relds);

                //获取索引信息
                CreateIndexEntity(table, indexds);
                //获取中文信息
                SetChName(chds, table);
            }
            return table;
        }
        /// <summary>
        /// 创建关系实体
        /// </summary>
        /// <param name="table"></param>
        /// <param name="relds"></param>
        private void CreateRelationshipEntity(DescriptionEntity table, IEnumerable<RelationshipInfo> relds)
        {
            if (relds == null)
                return;
            foreach (RelationshipInfo relrow in relds)
            {
                RelationshipInfo relrow1 = relrow;
                DataBaseEntity tbentity = table.ChildNodes.FirstOrDefault(o => o.Name == relrow1.TableName);
                    if (tbentity == null)
                        continue;

               
                    string name = relrow.ColumnName + "->["
                        + relrow.RelationTableName + "]."
                        + relrow.RelationColumnName;
                    if (relrow.IsCascadingDelete)
                        name += "(↓D)";
                    if (relrow.IsCascadingUpdate)
                        name += "(↑U)";
                    RelationshipEntity entity = new RelationshipEntity(name)
                                                {
                                                    RefKeyName = relrow.RefKeyName,
                                                    ColumnName = relrow.ColumnName,
                                                    RelationTableName = relrow.RelationTableName,
                                                    RelationColumnName = relrow.RelationColumnName,
                                                    IsCascadingUpdate = relrow.IsCascadingUpdate,
                                                    IsCascadingDelete = relrow.IsCascadingDelete
                                                };

                tbentity.ChildNodes[1].ChildNodes.Add(entity);
            }
        }
        /// <summary>
        /// 创建索引实体
        /// </summary>
        /// <param name="table"></param>
        /// <param name="indexds"></param>
        private void CreateIndexEntity(DescriptionEntity table, IEnumerable<IndexInfo> indexds)
        {
            if (indexds == null)
                return;
            foreach (IndexInfo indexrow in indexds)
            {
                IndexInfo indexrow1 = indexrow;
                DataBaseEntity tbentity = table.ChildNodes.FirstOrDefault(o => o.Name == indexrow1.TableName);
                if (tbentity == null)
                    continue;
                string indexName = indexrow.IndexName;
                bool isnew = false;
                IndexDescriptionEntity indesc = tbentity.ChildNodes[2].ChildNodes.FirstOrDefault(o => o.Name == indexName) as IndexDescriptionEntity;
                if (indesc == null)
                {
                    indesc = new IndexDescriptionEntity(indexName)
                                 {
                                     IsUnique = indexrow.IsUnique
                                 };
                    isnew = true;
                }
               
                    IndexEntity entity = new IndexEntity(indexrow.ColumnName)
                                             {
                                                 Sequence = indexrow.Sequence,
                                                 ColumnName = indexrow.ColumnName
                                             };
                    indesc.ChildNodes.Add(entity);
                
                if (isnew)
                {
                    tbentity.ChildNodes[2].ChildNodes.Add(indesc);
                }
            }
        }
     
     
        /// <summary>
        /// 创建视图实体
        /// </summary>
        /// <param name="vinfo"></param>
        /// <param name="cinfo"></param>
        /// <returns></returns>
        private DescriptionEntity CreateViewEntity(IEnumerable<ViewInfo> vinfo,List<ColumnInfo> cinfo)
        {
            DescriptionEntity view = new DescriptionEntity("视图");
            view.ImageUrl = "/Images/Tree/viewGroup.png";

            foreach (ViewInfo entity in vinfo)
            {

                ViewEntity viewEntity = new ViewEntity(entity.ViewName)
                                            {
                                                Content = entity.Content
                                            };
              
                //获取该表中的所有列
                ViewInfo entity1 = entity;
                foreach (ColumnInfo fEntity in cinfo.Where(o => o.TableName == entity1.ViewName))
                {
                    FieldEntity field = new FieldEntity(fEntity.ColumnName)
                                            {
                                                TypeName = fEntity.TypeName,
                                                Length = fEntity.Length,
                                                Scale = fEntity.DataScale,
                                                IsPrimaryKey = fEntity.IsPrimaryKey,
                                                IsNull = fEntity.IsNull
                                            };
                    viewEntity.ChildNodes.Add(field);
                }
                view.ChildNodes.Add(viewEntity);
            }

            return view;
        }

        /// <summary>
        /// 创建储存过程实体
        /// </summary>
        /// <param name="pinfo"></param>
        /// <param name="ppinfo"></param>
        /// <returns></returns>
        private DescriptionEntity CreateProcEntity(IEnumerable<ProcInfo> pinfo,List<ProcParamInfo> ppinfo)
        {
            DescriptionEntity proc = new DescriptionEntity("存储过程");
            proc.ImageUrl = "/Images/Tree/procGroup.png";


            foreach (ProcInfo entity in pinfo)
            {


                ProcEntity procEntity = new ProcEntity(entity.ProcName)
                                            {
                                                Content = entity.Content
                                            };

                ProcInfo entity1 = entity;
                foreach (ProcParamInfo ppentity in ppinfo.Where(o => o.ProcId == entity1.ProcId))
                {
                    ProcParamEntity paramEntity = new ProcParamEntity(ppentity.ProcParamName)
                                                      {
                                                          Length = ppentity.Length,
                                                          TypeName = ppentity.TypeName,
                                                          Status = ppentity.State,
                                                          Index = ppentity.ParamIndex,
                                                          Scale = ppentity.DataScale
                                                      };

                    procEntity.ChildNodes.Add(paramEntity);
                }



                proc.ChildNodes.Add(procEntity);
            }

            return proc;
        }
       
        /// <summary>
        /// 设置实体的中文名称
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="table"></param>
        private void SetChName(DataSet ds, DescriptionEntity table)
        {
            if (ds == null || ds.Tables.Count == 0)
            { return; }

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                DataRow dr1 = dr;
                DataBaseEntity tbentity = table.ChildNodes.FirstOrDefault(o => o.Name == dr1["TABLENAME"].ToString());
                if (tbentity == null)
                    continue;
                if (string.IsNullOrEmpty(tbentity.ChName))
                {
                    tbentity.ChName = dr["CHINATABLENAME"].ToString().Trim();
                }
                DataBaseEntity fielentity = tbentity.ChildNodes[0].ChildNodes.FirstOrDefault(o => o.Name == dr1["COLUMNNAME"].ToString());
                if (fielentity == null)
                    continue;
               
                fielentity.ChName = dr["CHINACOLUMNNAME"].ToString().Trim();
            }
        }

        /// <summary>
        /// 获取表和列的信息
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="log"> </param>
        public DbEntityCache GetDbInfos(ConnectionModel connection, Action<string, Color> log)
        {
            if (connection == null)
            {
                return null;
            }

            return GetDbEntitys(log, connection, false);

        }

        /// <summary>
        /// 添加 中文对照表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <param name="chtablename"></param>
        /// <param name="chfieldname"></param>
        /// <param name="fieldType"></param>
        /// <param name="fieldLength"></param>
        /// <param name="isPrimary"></param>
        public void SaveDescription(TableEntity table, FieldEntity field, string newChStr, bool isnewTable, TransactionType transaction = TransactionType.NoTransaction)
        {
            string sql = DBOperatorFactory.GetGrammar(SysCache.DBConnection).IsExistChTableSql;
            DataSet ds = ExecSqlToDS(sql);
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count == 0)
                {
                    if (!isnewTable)
                        return;
                    //创建表
                    sql = @"create table T_FIELD_DICTIONARY (
                           TABLENAME                        VARCHAR(50)        Not Null,        --表名
                           FIELDNAME                        VARCHAR(30)        Not Null,        --字段名
                           CHINESENAME                      VARCHAR(50)            Null,        --中文名
                           CHINESETABLENAME                 VARCHAR(40)            Null,        --中文表名
                           COMMENTARY                       VARCHAR(100)           Null,        --备注
                           COMPUTEFIELD                     VARCHAR(4)             Null,        --计算字段
                           COMPUTEFORMULA                   VARCHAR(60)            Null,        --计算公式
                           DICTIONARYID                     VARCHAR(10)            Null,        --字典ID
                           FIELDLENGTH                      INTEGER                Null,        --字段长度
                           FIELDPRECISION                   INTEGER                Null,        --字段精度
                           FIELDTYPE                        VARCHAR(20)            Null,        --字段类型
                           ISFORERGNKEY                     VARCHAR(2)             Null,        --外键
                           ISPRIMARY                        VARCHAR(2)             Null,        --主键
                           SHOW                             VARCHAR(4)             Null,        --显示
                           SHOWORDER                        INTEGER                Null,        --显示顺序
                           SHOWWIDTH                        INTEGER                Null,        --显示宽度
                           primary key  (TABLENAME,FIELDNAME)
                        )";
                    ExecSql(sql, SysCache.DBConnection, null, transaction);
                }
            }
            //
            sql = string.Format("select TABLENAME from T_FIELD_DICTIONARY where TABLENAME='{0}' and FIELDNAME ='{1}'", table.TransName, field.TransName);
            ds = ExecSqlToDS(sql, SysCache.DBConnection, null, transaction);
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    //更新
                    sql = "update T_FIELD_DICTIONARY set CHINESETABLENAME='{1}',CHINESENAME='{3}',FIELDTYPE='{4}',FIELDLENGTH={5},FIELDPRECISION={6},ISPRIMARY='{7}',ISFORERGNKEY='{8}' where TABLENAME='{0}' and FIELDNAME ='{2}' ";
                }
                else
                {
                    //插入
                    sql = "insert into T_FIELD_DICTIONARY(TABLENAME,CHINESETABLENAME,FIELDNAME,CHINESENAME,FIELDTYPE,FIELDLENGTH,FIELDPRECISION,ISPRIMARY,ISFORERGNKEY) values('{0}','{1}','{2}','{3}','{4}',{5},{6},'{7}','{8}')";
                }
                sql = string.Format(sql,
                        table.TransName,
                        table.TransChName,
                        field.TransName,
                        string.IsNullOrEmpty(newChStr) ? field.ChName : newChStr,
                        field.TypeName,
                        string.IsNullOrEmpty(field.Length) ? "NULL" : field.Length,
                        string.IsNullOrEmpty(field.Scale) ? "NULL" : field.Scale,
                        field.IsPrimaryKey ? "1" : "0",
                        field.IsForeignKey ? "1" : "0");
                ExecSql(sql, SysCache.DBConnection, null, transaction);
            }
        }
        /// <summary>
        /// 获取一个数据库的所有数据类型
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<TypeInfoEntity> GetTypeInfos(ConnectionModel connection = null)
        {
            if (connection == null)
                connection = SysCache.DBConnection;

            if (SysCache.TypeList != null)
                return SysCache.TypeList.ToList();

            List<TypeInfoEntity> typeList = new List<TypeInfoEntity>();
            ConnectDB(connection, (connect, dataaccess) =>
            {
                IDataStructure structue = dataaccess as IDataStructure;
                if (structue == null)
                    return;
                List<TypeNameInfos> list = new List<TypeNameInfos>();
                structue.FillingTypeInfos(list);
                list.ForEach(o => typeList.Add(new TypeInfoEntity
                {
                    Length = o.Length,
                    TypeName = o.TypeName,
                    Scale = o.Scale
                }));
            });
            SysCache.TypeList = typeList;
            return typeList;
        }
        #endregion

        #region 数据库连接，查询
        private IDbTransaction _transaction;
       
        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <param name="connect"></param>
        public void RollbackTransaction(ConnectionModel connect)
        {
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction = null;
            }
            IDataAccess dataaccess = HttpRuntimeCache.GetCache<IDataAccess>(connect.GetHashCode().ToString(CultureInfo.InvariantCulture));
            if (dataaccess != null)
            {
                dataaccess.Close();
                HttpRuntimeCache.RemoveCache(connect.GetHashCode().ToString(CultureInfo.InvariantCulture));
            }
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="connect"></param>
        public void CommitTransaction(ConnectionModel connect)
        {
            if (_transaction != null)
            {
                _transaction.Commit();
                _transaction = null;
            }
            IDataAccess dataaccess = HttpRuntimeCache.GetCache<IDataAccess>(connect.GetHashCode().ToString(CultureInfo.InvariantCulture));
            if (dataaccess != null)
            {
                dataaccess.Close();
                HttpRuntimeCache.RemoveCache(connect.GetHashCode().ToString(CultureInfo.InvariantCulture));
            }
        }

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="connect"></param>
        /// <param name="exec"> </param>
        /// <param name="transaction"> </param>
        public void ConnectDB(ConnectionModel connect, Action<ConnectionModel, IDataAccess> exec, TransactionType transaction = TransactionType.NoTransaction)
        {
            if (connect == null)
            {
                throw new Exception("当前链接已断开，请重新连接.");
            }
            IDataAccess dataaccess = null;
            if (transaction== TransactionType.Transaction)
            {
                dataaccess = HttpRuntimeCache.GetCache<IDataAccess>(connect.GetHashCode().ToString(CultureInfo.InvariantCulture));
            }
            if (dataaccess == null)
            {
                //生成连接字符串
                string connectStr;
                PersistenceProperty.DatabaseType = DBOperatorFactory.GetLocalGrammar(connect).GetConnectionStr(out connectStr);
                connect.ConnectionStr = connectStr;
                PersistenceProperty.ConnectionString = connectStr;
                dataaccess = DataAccessFactory.Instance.CreateDataAccess();
                if (transaction == TransactionType.Transaction)
                {
                    HttpRuntimeCache.SetCache(connect.GetHashCode().ToString(CultureInfo.InvariantCulture), dataaccess, 5);
                }
            }


            dataaccess.Open();

            if (transaction == TransactionType.Transaction && _transaction == null)
            {
                _transaction = dataaccess.BeginTransaction();
            }
            try
            {
                if (exec != null)
                {
                    exec(connect, dataaccess);
                }
            }
            finally
            {
                if (transaction == TransactionType.NoTransaction)
                {
                    dataaccess.Close();
                }
            }
        }
        public void ConnectDB(ConnectionModel connect)
        {
            if (connect == null)
            {
                throw new Exception("当前链接无效，请重新配置.");
            }
            //生成连接字符串
            string connectStr;
            PersistenceProperty.DatabaseType = DBOperatorFactory.GetLocalGrammar(connect).GetConnectionStr(out connectStr);
            connect.ConnectionStr = connectStr;
            PersistenceProperty.ConnectionString = connectStr;
            IDataAccess dataaccess = DataAccessFactory.Instance.CreateDataAccess();
            dataaccess.Open();
            dataaccess.Close();
            SysCache.DBConnection = connect;
        }
        public void ExecSql(string sql, ConnectionModel connect = null, List<DBParam> paramlist = null, TransactionType transaction = TransactionType.NoTransaction)
        {
            if (connect == null)
                connect = SysCache.DBConnection;
            ConnectDB(connect, (c, i) =>
            {
                if (paramlist != null && paramlist.Count > 0)
                {
                    QueryParameterCollection qpc = new QueryParameterCollection();
                    paramlist.ForEach(o =>
                    {
                        if (string.IsNullOrEmpty(o.TypeName))
                        {
                            qpc.Add("@" + o.Name, o.Value);
                        }
                        else
                        {
                            qpc.Add(new QueryParameter("@" + o.Name, o.Value, o.TypeName, ParameterDirection.Input));
                        }
                    });
                    i.ExecuteNonQuery(sql, qpc);
                }
                else
                {
                    i.ExecuteNonQuery(sql);
                }
            }, transaction);
        }
        public object ExecScalar(string sql, ConnectionModel connect = null, List<DBParam> paramlist = null, TransactionType transaction = TransactionType.NoTransaction)
        {
            if (connect == null)
                connect = SysCache.DBConnection;
            object result = null;
            ConnectDB(connect, (c, i) =>
            {
                if (paramlist != null && paramlist.Count > 0)
                {
                    QueryParameterCollection qpc = new QueryParameterCollection();
                    paramlist.ForEach(o =>
                    {
                        if (string.IsNullOrEmpty(o.TypeName))
                        {
                            qpc.Add(o.Name, o.Value);
                        }
                        else
                        {
                            qpc.Add(new QueryParameter(o.Name, o.Value, o.TypeName, ParameterDirection.Input));
                        }
                    });
                    result = i.ExecuteScalar(sql, qpc);
                }
                else
                {
                    result = i.ExecuteScalar(sql);
                }
            }, transaction);
            return result;
        }
        public int ExecNonQuery(string sql, ConnectionModel connect = null, List<DBParam> paramlist = null, TransactionType transaction = TransactionType.NoTransaction)
        {
            if (connect == null)
                connect = SysCache.DBConnection;
            int result = 0;
            ConnectDB(connect, (c, i) =>
            {
                if (paramlist != null && paramlist.Count > 0)
                {
                    QueryParameterCollection qpc = new QueryParameterCollection();
                    paramlist.ForEach(o =>
                    {
                        if (string.IsNullOrEmpty(o.TypeName))
                        {
                            qpc.Add(o.Name, o.Value);
                        }
                        else
                        {
                            qpc.Add(new QueryParameter(o.Name, o.Value, o.TypeName, ParameterDirection.Input));
                        }
                    });
                    result = i.ExecuteNonQuery(sql, qpc);
                }
                else
                {
                    result = i.ExecuteNonQuery(sql);
                }
            }, transaction);
            return result;
        }
        public DataSet ExecSqlToDS(string sql, ConnectionModel connect = null, List<DBParam> paramlist = null, TransactionType transaction = TransactionType.NoTransaction)
        {
            DataSet ds = null;
            if (connect == null)
                connect = SysCache.DBConnection;
            ConnectDB(connect, (c, i) =>
            {
                ds = new DataSet();
                if (paramlist != null && paramlist.Count > 0)
                {
                    QueryParameterCollection qpc = new QueryParameterCollection();
                    paramlist.ForEach(o =>
                    {
                        if (string.IsNullOrEmpty(o.TypeName))
                        {
                            qpc.Add(o.Name, o.Value);
                        }
                        else
                        {
                            qpc.Add(new QueryParameter(o.Name, o.Value, o.TypeName, ParameterDirection.Input));
                        }
                    });
                    ds = i.ExecuteDataset(sql, qpc);
                }
                else
                {
                    ds = i.ExecuteDataset(sql);
                }
            }, transaction);
            return ds;
        }
        public DataSet ExecProc(string procName, List<DBParam> paramlist, out Dictionary<string, object> outdic, ConnectionModel connect = null, TransactionType transaction = TransactionType.NoTransaction)
        {
            DataSet result = null;
            if (connect == null)
                connect = SysCache.DBConnection;
            Dictionary<string, object> dic = new Dictionary<string, object>();
            ConnectDB(connect, (c, i) =>
            {
                if (paramlist != null && paramlist.Count > 0)
                {
                    QueryParameterCollection qpc = new QueryParameterCollection();
                    paramlist.ForEach(o =>
                    {
                        QueryParameter qp=null;
                        if (string.IsNullOrEmpty(o.TypeName))
                        {
                            qp = new QueryParameter(o.Name, o.Value);
                            qp.Direction = (ParameterDirection)o.ParamType;
                        }
                        else
                        {
                             qp = new QueryParameter(o.Name, o.Value, o.TypeName,(ParameterDirection)o.ParamType);
                        }
                        qpc.Add(qp);
                    });
                    result = i.ExecuteDataset(CommandType.StoredProcedure, procName, qpc);
                    
                    foreach(DBParam p in paramlist.Where(o=>o.ParamType == 2))
                    {
                        dic.Add(p.Name, qpc[p.Name].Value);
                    }
                }
                else
                {
                    result = i.ExecuteDataset(CommandType.StoredProcedure, procName);
                }
            }, transaction);
            outdic = dic;
            return result;
        }

        /// <summary>
        /// 检查连接是否正常
        /// </summary>
        /// <param name="connect"></param>
        /// <returns>"":正常  "错误内容":出错</returns>
        public string IsConnectDB(ConnectionModel connect)
        {
            try
            {
                if (connect == null)
                {
                    return "连接实体不能为空";
                }
                
                //生成连接字符串
                string connectStr;
                PersistenceProperty.DatabaseType = DBOperatorFactory.GetLocalGrammar(connect).GetConnectionStr(out connectStr);
                PersistenceProperty.ConnectionString = connectStr;
                IDataAccess dataaccess = DataAccessFactory.Instance.CreateDataAccess();
                dataaccess.Open();
                dataaccess.Close();
                return string.Empty;
            }
            catch(Exception ex)
            {
                return ex.Message;
            }
        }

        #endregion
    }
}
